﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using SubSonic;
using CXDB;

public partial class excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string filepath = Server.MapPath("~/excelfile/123.xls");
        foreach (string str in ExcelSheetName(filepath))
        {
            DataSet ds = ExcelDataSource(filepath, str);
            foreach (DataRow dr in ds.Tables["TABLE"].Rows)
            {
                Query q=new Query(CxCompanyBase.Schema);
                q.AddWhere("companyid", dr[0].ToString());
                if (q.GetRecordCount() == 0)
                {
                    CxCompanyBase.Insert(
                        dr[0].ToString(),
                        dr[1].ToString(),
                        dr[2].ToString(),
                        int.Parse(dr[3].ToString()),
                        int.Parse(dr[4].ToString()),
                        dr[5].ToString(),
                        int.Parse(dr[6].ToString()),
                        80,
                        "A",
                        "0",
                        0,
                        0,
                        DateTime.Now,
                        Guid.NewGuid());
                }
            }
        }
    }
    public DataSet ExcelDataSource(string filepath, string sheetname)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        return ds;
    }
    public ArrayList ExcelSheetName(string filepath)
    {
        ArrayList al = new ArrayList();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable sheetNames = conn.GetOleDbSchemaTable
        (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        conn.Close();
        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
        return al;
    }
}
